package alex;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.FlowLayout;
import java.awt.Font;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.concurrent.ExecutionException;

import javax.net.ssl.HttpsURLConnection;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.SwingUtilities;
import javax.swing.SwingWorker;
import javax.swing.WindowConstants;

import com.csvreader.CsvReader;

public class StockAutoGet extends JFrame implements ActionListener {

	private static final long serialVersionUID = 627647391424984503L;

	private static final int FRAME_DEFALUT_WIDTH = 600;
	private static final int FRAME_DEFALUT_HEIGHT = 700;

	private static final boolean DEBUG = false;
	private static int DownloadSpeed = 0;
	
	//Java Swing
	private JButton dlTseDataBtn;
	private JButton dlOtcDataBtn;
	private JButton dlDataBtn;
	private JButton updateDataBtn;
	private JButton closeBtn;
	private JPanel updatePanel;
	private JScrollPane tableScrollPane;

	private JTextArea jTextArea;

	private ResourceBundle bundle;
	
	private static String initDate;			//預設之資料初始日期
	private static String downloadPath;		//預設之下載目錄
	private static String backupPath;		//預設之備份目錄
	private static String stockTsePath;		//預設TSE之下載目錄
	private static String tseBackupPath;	//預設TSE備份目錄
	private static String stockOtcPath;		//預設OTC之下載目錄
	private static String otcBackupPath;	//預設OTC備份目錄
	private static boolean isUpdateTime = false;
	private static boolean isTseFinish = false;
	private static boolean isOtcFinish = false;
	
	private static Integer tseCount = 0;
	private static Integer otcCount = 0;
	
	private Connection conn = null;

	private List<File> tseFilePathList = new ArrayList<File>();
	private List<File> otcFilePathList = new ArrayList<File>();

	public StockAutoGet() {
		super();
		
		//顯示畫面
		initGUI();
		
//		initializeComponent();
		
		//盤後才允許更新資料，現在時分
		Calendar calendar = Calendar.getInstance();
		int nowYear = calendar.get(Calendar.YEAR);
		int nowMonth = calendar.get(Calendar.MONTH) + 1;
		int nowDay = calendar.get(Calendar.DAY_OF_MONTH);
		int nowHour = calendar.get(Calendar.HOUR_OF_DAY);
		int nowMinute = calendar.get(Calendar.MINUTE);
		
		ResourceBundle bundleCfg = ResourceBundle.getBundle("resource.system");
		String defalutUpdateDuring = bundleCfg.getString("defalut.update.start.time");
		String[] updateTime = defalutUpdateDuring.split(":");
		int dHour = Integer.valueOf(updateTime[0]);
		int dMinute = Integer.valueOf(updateTime[1]);
		
		boolean isValidTime = (nowHour >= dHour && nowMinute >= dMinute) ? true : false;
		
		String forceOpen = bundleCfg.getString("defalut.update.force.open");
		if("true".equals(forceOpen) || "Y".equals(forceOpen)){
			isValidTime = true;
		}
		
		if(isValidTime){
			dlTseDataBtn.setEnabled(true);
			dlTseDataBtn.setText("下載TSE資料檔");
			dlOtcDataBtn.setEnabled(true);
			dlOtcDataBtn.setText("下載OTC資料檔");
			
			Font font = new Font("", Font.BOLD, 14);
			jTextArea.setFont(font);
			
			isUpdateTime = true;
		}else{
			dlTseDataBtn.setText("非允許下載時間");
			dlTseDataBtn.setEnabled(false);
			dlOtcDataBtn.setText("非允許下載時間");
			dlOtcDataBtn.setEnabled(false);
			Font font = new Font("", Font.BOLD, 14);
			jTextArea.setFont(font);
			jTextArea.setEnabled(false);
			jTextArea.append("=== 請於每日合法下載時間("+defalutUpdateDuring+" ~ 00:00)內執行 ===\n");
		}
		
		if(isUpdateTime){
			//測試目前url有效性
			jTextArea.append("=== 下載連結有效測試 ===\n");
			String aTseYear = String.format("%03d", nowYear);
			String aOtcYear = String.format("%03d", nowYear - 1911);
			String aMonth = String.format("%02d", nowMonth);
			String aDay = String.format("%02d", nowDay);
			//改用基準日當測試url的日期
//			aTseYear = "2012";
//			aOtcYear = "101";
//			aMonth = "02";
//			aDay = "01";
			
			String defalutDate = bundleCfg.getString("stock.data.init.date");  //預設之資料初始日期
			String[] initDateArr = defalutDate.split("/"); 
			aOtcYear = initDateArr[0];	//預設年
			aMonth = this.appendZero(initDateArr[1]);	//預設月
			aDay = this.appendZero(initDateArr[2]);	//預設日
			
//			String tseUrl = "http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX3_print.php?genpage=genpage/Report"+aTseYear+aMonth+"/A112"+aTseYear+aMonth+aDay+"ALLBUT0999_1.php&type=csv";
//			String otcUrl = "http://www.otc.org.tw/ch/stock/aftertrading/otc_quotes_no1430/SQUOTE_AL_"+aOtcYear+aMonth+aDay+".CSV";
//			String otcUrl = "http://www.otc.org.tw/ch/stock/aftertrading/DAILY_CLOSE_quotes/stk_quote_download.php?d="+aOtcYear+"/"+aMonth+"/"+aDay+"&s=0";
//			String otcUrl = "http://www.otc.org.tw/ch/stock/aftertrading/DAILY_CLOSE_quotes/stk_quote_download.php?d=103/01/08&s=0";
			
			String tseUrl = String.format(Constant.TSE_URL, aTseYear, aMonth, aTseYear, aMonth, aDay);
			String otcUrl = String.format(Constant.OTC_URL, aOtcYear, aMonth, aDay);
			
//			Date lastTimeTse = queryLastUpdateTime(Constant.STOCK_CLASS_TSE);
//			Date lastTimeOtc = queryLastUpdateTime(Constant.STOCK_CLASS_OTC);
//			SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
//			
//			Calendar tseCalendar = Calendar.getInstance();
//			tseCalendar.setTime(lastTimeTse);
//			int tseYear = tseCalendar.get(Calendar.YEAR);
//			int tseMonth = tseCalendar.get(Calendar.MONTH) + 1;
//			int tseday = tseCalendar.get(Calendar.DAY_OF_MONTH);
//			String aTseYear = String.format("%03d", tseYear);
//			String aTseMonth = String.format("%02d", tseMonth);
//			String aTseDay = String.format("%02d", tseday);
//			String tseUrl = "http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX3_print.php?genpage=genpage/Report"+aTseYear+aTseMonth+"/A112"+aTseYear+aTseMonth+aTseDay+"ALLBUT0999_1.php&type=csv";
//			
//			Calendar otcCalendar = Calendar.getInstance();
//			otcCalendar.setTime(lastTimeOtc);
//			int otcYear = otcCalendar.get(Calendar.YEAR);
//			int otcMonth = otcCalendar.get(Calendar.MONTH) + 1;
//			int otcday = otcCalendar.get(Calendar.DAY_OF_MONTH);
//			String aOtcYear = String.format("%03d", otcYear - 1911);
//			String aOtcMonth = String.format("%02d", otcMonth);
//			String aOtcDay = String.format("%02d", otcday);
//			String otcUrl = "http://www.otc.org.tw/ch/stock/aftertrading/otc_quotes_no1430/SQUOTE_AL_"+aOtcYear+aOtcMonth+aOtcDay+".CSV";
			
			try {
				Map<String, Object> tseUrlMap = this.checkUrlStatus(tseUrl);
				boolean tseIsConnected = (Boolean)tseUrlMap.get("isConnected");
				if (tseIsConnected) {
					jTextArea.append("證券交易所：正常\n");
					dlTseDataBtn.setEnabled(true);
				} else {
					Integer tseResponseCode = (Integer)tseUrlMap.get("responseCode");
					StringBuilder tseErrMsg = new StringBuilder("證券交易所：異常! responseCode:");
					tseErrMsg.append(tseResponseCode).append("\n");
					jTextArea.append(tseErrMsg.toString());
					dlTseDataBtn.setEnabled(false);
					updateDataBtn.setEnabled(false);
				}
				
				Map<String, Object> otcUrlMap = this.checkUrlStatus(otcUrl);
				boolean otcIsConnected = (Boolean)otcUrlMap.get("isConnected");
				if (otcIsConnected) {
					jTextArea.append("櫃買中心：正常\n");
					dlOtcDataBtn.setEnabled(true);
				} else {
					Integer otcResponseCode = (Integer)otcUrlMap.get("responseCode");
					StringBuilder otcErrMsg = new StringBuilder("櫃買中心：異常! responseCode:");
					otcErrMsg.append(otcResponseCode).append("\n");
					jTextArea.append(otcErrMsg.toString());
					dlOtcDataBtn.setEnabled(false);
					updateDataBtn.setEnabled(false);
				}
				
				if (checkUrl(tseUrl) && checkUrl(otcUrl)) {
					dlDataBtn.setEnabled(true);
				}else{
					jTextArea.setForeground(Color.RED);
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}
			jTextArea.append("=== 下載連結測試完成 ===\n\n");
			
			//現在日期
			String showNow = "現在日期:%s/%s/%s\n\n";
			showNow = String.format(showNow, nowYear, nowMonth, nowDay);
			jTextArea.append(showNow);
			
			//顯示上次更新進度
			boolean isOpen = this.connectDatabase();
			if(isOpen){
				boolean isTseUpdate = showLastTseLog(true);
				if(!isTseUpdate){
					jTextArea.append("您的TSE資料為最新版，已不需更新!\n");
					dlTseDataBtn.setEnabled(false);
				}else{
					jTextArea.append("請更新TSE資料...\n");
					dlTseDataBtn.setEnabled(true);
				}
				
				jTextArea.append("\n");
				boolean isOtcUpdate = showLastOtcLog(true);
				if(!isOtcUpdate){
					jTextArea.append("您的OTC資料為最新版，已不需更新!\n");
					dlOtcDataBtn.setEnabled(false);
				}else{
					jTextArea.append("請更新OTC資料...\n");
					dlOtcDataBtn.setEnabled(false);
				}
				
				//下載資料檔按鈕開關判斷
				if(!isTseUpdate && !isOtcUpdate){
					dlDataBtn.setEnabled(false);
				}else{
					dlDataBtn.setEnabled(true);
				}
				
				jTextArea.append("\n");
				if(!isTseUpdate && !isOtcUpdate){
					updateDataBtn.setEnabled(false);
				}
			}else{
				jTextArea.append("資料庫連結失敗!!!\n請查看資料庫是否啟動、帳號密碼是否正確\n");
				jTextArea.setForeground(Color.RED);
				dlDataBtn.setEnabled(false);
			}
			
//			updateDataBtn.setEnabled(true);  //test
		}
		
	}
	
	/**
	 * 初始UI畫面
	 */
	private void initGUI() {
		try {
			BorderLayout borderLayout = new BorderLayout();
			getContentPane().setLayout(borderLayout);
			setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);

			JPanel nouthPanel = new JPanel();
			GridLayout gridLayout = new GridLayout(1, 3);
			gridLayout.setRows(1);
			gridLayout.setColumns(3);
			nouthPanel.setLayout(gridLayout);
			{
				updatePanel = new JPanel();
				FlowLayout flowLayout1 = new FlowLayout();
				flowLayout1.setAlignment(FlowLayout.LEFT);
				updatePanel.setLayout(flowLayout1);
				nouthPanel.add(updatePanel);
				{
					dlDataBtn = new JButton();
					updatePanel.add(dlDataBtn);
					dlDataBtn.setText("下載資料檔");
					dlDataBtn.setEnabled(false);  //先disable
					dlDataBtn.addActionListener(new ActionListener() {
						public void actionPerformed(ActionEvent evt) {
							dlDataBtn_ActionPerformed(evt);
						}
					});
					dlDataBtn.setVisible(true);
					
					dlTseDataBtn = new JButton();
					updatePanel.add(dlTseDataBtn);
					dlTseDataBtn.setText("下載TSE資料檔");
					dlTseDataBtn.addActionListener(new ActionListener() {
						public void actionPerformed(ActionEvent evt) {
							dlTseBtn_ActionPerformed(evt);
						}
					});
					dlTseDataBtn.setVisible(false);
					
					dlOtcDataBtn = new JButton();
					updatePanel.add(dlOtcDataBtn);
					dlOtcDataBtn.setText("下載OTC資料檔");
					dlOtcDataBtn.setEnabled(true);  //先disable
					dlOtcDataBtn.addActionListener(new ActionListener() {
						public void actionPerformed(ActionEvent evt) {
							dlOtcBtn_ActionPerformed(evt);
						}
					});
					dlOtcDataBtn.setVisible(false);
					
					updateDataBtn = new JButton();
					updatePanel.add(updateDataBtn);
					updateDataBtn.setText("更新資料庫");
					updateDataBtn.setEnabled(false);  //預設不開啟，下載完檔案再開啟
					updateDataBtn.addActionListener(new ActionListener() {
						public void actionPerformed(ActionEvent evt) {
							updateDataBtn_ActionPerformed(evt);
						}
					});
					
					closeBtn = new JButton();
					updatePanel.add(closeBtn);
					closeBtn.setText("關閉");
					closeBtn.setEnabled(true);
					closeBtn.addActionListener(new ActionListener() {
						public void actionPerformed(ActionEvent evt) {
							closeBtn_ActionPerformed(evt);
						}
					});
					closeBtn.setVisible(false);
				}
			}
			getContentPane().add(nouthPanel, BorderLayout.NORTH);

			{
				jTextArea = new JTextArea();
				jTextArea.setRows(5);
				jTextArea.setColumns(10);
				jTextArea.setForeground(Color.blue);
				tableScrollPane = new JScrollPane(jTextArea);
				jTextArea.setAutoscrolls(true);
			}
			getContentPane().add(tableScrollPane, BorderLayout.CENTER);

			pack();
			this.setSize(FRAME_DEFALUT_WIDTH, FRAME_DEFALUT_HEIGHT);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	private void initializeComponent() {
		this.addWindowListener(new WindowAdapter() {
//			/*
//			 * Invoked when a window has been opened.
//			 */
//			public void windowOpened(WindowEvent e) {
//				System.out.println("Window Opened Event");
//			}
//
//			/*
//			 * Invoked when a window is in the process of being closed.
//			 * The close operation can be overridden at this point.(non-Javadoc)
//			 */
//			public void windowClosing(WindowEvent e) {
//				System.out.println("Window Closing Event");
//			}
//
//			/*
//			 * Invoked when a window has been closed.
//			 */
//			public void windowClosed(WindowEvent e) {
//				System.out.println("Window Close Event");
//			}
//
//			/*
//			 * Invoked when a window is iconified.
//			 */
//			public void windowIconified(WindowEvent e) {
//				System.out.println("Window Iconified Event");
//			}
//
//			/*
//			 * Invoked when a window is de-iconified.
//			 */
//			public void windowDeiconified(WindowEvent e) {
//				System.out.println("Window Deiconified Event");
//			}
//
//			/*
//			 * Invoked when a window is activated.
//			 */
//			public void windowActivated(WindowEvent e) {
//				System.out.println("Window Activated Event");
//			}
//
//			/*
//			 * Invoked when a window is de-activated.
//			 */
//			public void windowDeactivated(WindowEvent e) {
//				System.out.println("Window Deactivated Event");
//			}
//
//			/*
//			 * 
//			 * Invoked when a window state is changed.
//			 */
//			public void windowStateChanged(WindowEvent e) {
//				System.out.println("Window State Changed Event");
//			}
//
//			/*
//			 * Invoked when the Window is set to be the focused Window, which means that the Window,
//			 *  or one of its subcomponents, will receive keyboard events.
//			 */
//			public void windowGainedFocus(WindowEvent e) {
//				System.out.println("Window Gained Focus Event");
//			}
//
//			/*
//			 * Invoked when the Window is no longer the focused Window, which means that keyboard events 
//			 * will no longer be delivered to the Window or any of its subcomponents.
//			 */
//			public void windowLostFocus(WindowEvent e) {
//				System.out.println("Window Lost Focus Event");
//			}
		});
	}
	
	/**
	 * 讀取上一次TSE更新記錄
	 */
	private boolean showLastTseLog(boolean isShowMsg){
		boolean isNeedUpdateTse = false;
		
		Calendar calendar = Calendar.getInstance();
		
		try {
			if(isShowMsg){
				jTextArea.append("讀取上一次TSE更新記錄...\n");
			}
			
			Date lastTimeTse = queryLastUpdateTime(Constant.STOCK_CLASS_TSE);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
			Date now = sdf.parse(sdf.format(calendar.getTime()));
			
			String lastUpdateTimeTse = (lastTimeTse != null) ? sdf.format(lastTimeTse) : null;
			if (lastUpdateTimeTse != null) {
				if(isShowMsg){
					jTextArea.append("上市股票資訊：更新記錄日期為" + lastUpdateTimeTse + "\n");
				}
				
				//判斷是否需要更新
				if(lastTimeTse.before(now)){
					isNeedUpdateTse = true;
				}
			} else {
				if(isShowMsg){
					jTextArea.append("上市股票資訊：您沒有任何上市股票更新記錄...\n");
				}
				isNeedUpdateTse = true;
			}
			
		} catch (Exception e) {
			e.getStackTrace();
		}
		
		return (isNeedUpdateTse) ? true: false;
	}
	
	/**
	 * 讀取上一次OTC更新記錄
	 */
	private boolean showLastOtcLog(boolean isShowMsg){
		boolean isNeedUpdateOtc = false;
		
		Calendar calendar = Calendar.getInstance();
		
		try {
			if(isShowMsg){
				jTextArea.append("讀取上一次OTC更新記錄...\n");
			}
			
			Date lastTimeOtc = queryLastUpdateTime(Constant.STOCK_CLASS_OTC);
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
			Date now = sdf.parse(sdf.format(calendar.getTime()));
			
			String lastUpdateTimeOtc = (lastTimeOtc != null) ? sdf.format(lastTimeOtc) : null;
			if (lastUpdateTimeOtc != null) {
				if(isShowMsg){
					jTextArea.append("上櫃股票資訊：更新記錄日期為" + lastUpdateTimeOtc + "\n");
				}
				//判斷是否需要更新
				if(lastTimeOtc.before(now)){
					isNeedUpdateOtc = true;
				}
			} else {
				if(isShowMsg){
					jTextArea.append("上櫃股票資訊：您沒有任何上櫃股票更新記錄...\n");
				}
				isNeedUpdateOtc = true;
			}
			
		} catch (Exception e) {
			e.getStackTrace();
		}
		
		return (isNeedUpdateOtc) ? true: false;
	}
	
	/**
	 * main程式
	 * @param args
	 */
	public static void main(String[] args) {
		JFrame.setDefaultLookAndFeelDecorated(true);
		
		SwingUtilities.invokeLater(new Runnable() {
			public void run() {
				StockAutoGet stockTable = new StockAutoGet();
				stockTable.setTitle("StockAutoGet");
				stockTable.setLocationRelativeTo(null);
				stockTable.setVisible(true);
				
				if(isUpdateTime){
					initDate = stockTable.bundle.getString("stock.data.init.date");			//預設之資料初始日期
//					stockTsePath = stockTable.bundle.getString("stock.tse.path");			//預設TSE之下載目錄
//					stockOtcPath = stockTable.bundle.getString("stock.otc.path");			//預設OTC之下載目錄
//					tseBackupPath = stockTable.bundle.getString("stock.tse.backup.path");	//預設TSE備份目錄
//					otcBackupPath = stockTable.bundle.getString("stock.otc.backup.path");	//預設OTC備份目錄
					
					downloadPath = stockTable.bundle.getString("stock.download.path");
					backupPath = stockTable.bundle.getString("stock.backup.path");
					
					stockTsePath = downloadPath.concat(Constant.STOCK_CLASS_TSE).concat("/");
					stockOtcPath = downloadPath.concat(Constant.STOCK_CLASS_OTC).concat("/");
				}
			}
				
		});
	}
	
	/**
	 * 下載檔案按鈕
	 * @param evt
	 */
	private void dlDataBtn_ActionPerformed(ActionEvent evt) {
		boolean isTseUpdate = showLastTseLog(false);
		if(!isTseUpdate){
			dlTseDataBtn.setEnabled(false);
			jTextArea.append("您的TSE資料為最新版，已不需更新!\n");
			jTextArea.setCaretPosition(jTextArea.getText().length());
			return;
		}
		boolean isNeedUpdate = showLastOtcLog(false);
		if(!isNeedUpdate){
			dlOtcDataBtn.setEnabled(false);
			jTextArea.append("您的OTC資料為最新版，已不需更新!\n");
			jTextArea.setCaretPosition(jTextArea.getText().length());
			return;
		}
		
		try {
			dlDataBtn.setEnabled(false);
			toDownload();
			
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 下載TSE檔案按鈕
	 * @param evt
	 */
	private void dlTseBtn_ActionPerformed(ActionEvent evt) {
		boolean isTseUpdate = showLastTseLog(false);
		if(!isTseUpdate){
			dlTseDataBtn.setEnabled(false);
			jTextArea.append("您的TSE資料為最新版，已不需更新!\n");
			jTextArea.setCaretPosition(jTextArea.getText().length());
			return;
		}
		
		try {
			dlTseDataBtn.setEnabled(false);
			dlOtcDataBtn.setEnabled(false);
			toDownloadTse();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 下載OTC檔案按鈕
	 * @param evt
	 */
	private void dlOtcBtn_ActionPerformed(ActionEvent evt) {
		//讀取上一次更新記錄
		boolean isNeedUpdate = showLastOtcLog(false);
		if(!isNeedUpdate){
			dlOtcDataBtn.setEnabled(false);
//			wDataBtn.setEnabled(false);
			jTextArea.append("您的OTC資料為最新版，已不需更新!\n");
			jTextArea.setCaretPosition(jTextArea.getText().length());
			return;
		}

		try {
			dlTseDataBtn.setEnabled(false);
			dlOtcDataBtn.setEnabled(false);
	    	toDownloadOtc();
	    	
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 更新資料庫
	 * @param evt
	 */
	private void updateDataBtn_ActionPerformed(ActionEvent evt) {
		try {
			//改成先搬移檔案至備份目錄，再寫入資料庫方式，避免因寫入資料庫後檔案被鎖住無法搬移的情形
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
			String today = sdf.format(new Date());
			
			tseBackupPath = backupPath.concat(today).concat("/").concat(Constant.STOCK_CLASS_TSE).concat("/");
			otcBackupPath = backupPath.concat(today).concat("/").concat(Constant.STOCK_CLASS_OTC).concat("/");
			
			System.out.println("開始移動TSE下載資料檔案至備份目錄...");
			FileUtils.fileMove(stockTsePath, tseBackupPath);
			System.out.println("開始移動OTC下載資料檔案至備份目錄...");
			FileUtils.fileMove(stockOtcPath, otcBackupPath);
			
			jTextArea.append("=== 開始寫入資料庫 ===\n");
			System.out.println("=== 開始寫入資料庫 ===\n");
			
			isTseFinish = readAndInsertTse(tseBackupPath);
			isOtcFinish = readAndInsertOtc(otcBackupPath);
			
			if(isTseFinish && isOtcFinish){
				jTextArea.append("=== 完成資料庫更新 ===\n\n"); 
				System.out.println("=== 完成資料庫更新 ===\n\n");
				
			}else{
				jTextArea.append("=== 資料庫更新發生錯誤 ===\n"); 
				System.out.println("=== 資料庫更新發生錯誤 ===\n\n");
			}
			
			updateDataBtn.setEnabled(false);
			
			closeDatabase();
			
		} catch (Exception e) {
			e.printStackTrace();
			jTextArea.append("=== 資料庫更新發生錯誤 ===\n"); 
			jTextArea.append(e.getMessage()+"\n");
			System.out.println("=== 資料庫更新發生錯誤 ===\n\n");
		}
	}
	
	private void closeBtn_ActionPerformed(ActionEvent evt) {
		closeDatabase();
		setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
	}
	
	/**
	 * 連結DB
	 * @return
	 */
	public boolean connectDatabase() {
		dlTseDataBtn.setEnabled(false);
		boolean connect = false;
		
		bundle = ResourceBundle.getBundle("resource.system");
		String jdbcDriver = bundle.getString("jdbc.driverClass");
		String jdbcUrl = bundle.getString("jdbc.jdbcUrl");
		String jdbcUsername = bundle.getString("jdbc.username");
		String jdbcPassword = bundle.getString("jdbc.password");

		try {
			Class.forName(jdbcDriver);
			conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
			
			connect = true;
		} catch (ClassNotFoundException e) {
			e.toString();
		} catch (SQLException x) {
			x.toString();
		}
		
		return connect;
	}

	/**
	 * 關閉DB
	 */
	public boolean closeDatabase() {
		boolean disConnect = false;
		if (conn != null) {
			try {
				conn.close();
				
				disConnect = true;
			} catch (SQLException e) {
				e.toString();
			}
		}
		
		return disConnect;
	}

	/**
	 * 關閉畫面時關閉DB
	 * @param e
	 */
	public void windowClosed(WindowEvent e) {
		this.closeDatabase();
	}
	
	public Date queryLastUpdateTime(String stockClass){
		if(stockClass == null || stockClass.length() == 0){
			return null;
		}
		
		//查詢最後一筆更新資料
		PreparedStatement pstmt = null;
		Date fileTime = null;
		try {
			String sql = "select MAX(FILE_TIME) as FILE_TIME from stock_update_log where stock_class = '"+stockClass+"' ";
			pstmt = conn.prepareStatement(sql);
		
			ResultSet result = pstmt.executeQuery();
			
			while(result.next()) { 
			    fileTime = result.getDate("FILE_TIME");
			    break;
			}
			
		} catch (SQLException e1) {
			e1.printStackTrace();
		}finally{
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return fileTime;
	}

	@Override
	public void actionPerformed(ActionEvent e) {
	}

	/**
	 * 取得TSE目錄所有檔案列表
	 * @param file
	 * @return
	 */
	public List<File> getTseFilePathList(File file) {
		if (file.isFile()) {
			// filePathList.add(file.getPath());
			tseFilePathList.add(file);
		} else if (file.isDirectory()) {
			File[] files = file.listFiles();
			for (File f : files) {
				getTseFilePathList(f);
			}
		}
		return tseFilePathList;
	}
	
	/**
	 * 取得OTC目錄所有檔案列表
	 * @param file
	 * @return
	 */
	public List<File> getOtcFilePathList(File file) {
		if (file.isFile()) {
			otcFilePathList.add(file);
		} else if (file.isDirectory()) {
			File[] files = file.listFiles();
			for (File f : files) {
				getOtcFilePathList(f);
			}
		}
		return otcFilePathList;
	}

	public boolean isInteger(String input) {
		boolean isDouble = false;
		if (input == null || input == "") {
			return isDouble;
		}

		try {
			Integer.parseInt(input);
			isDouble = true;
		} catch (Exception e) {
			isDouble = false;
		}

		return isDouble;
	}

	public boolean isDouble(String input) {
		boolean isDouble = false;
		try {
			Double.parseDouble(input);
			isDouble = true;
		} catch (Exception e) {
			isDouble = false;
		}

		return isDouble;
	}
	
	public String trimSpace(String input){
		String output = "";
		if(input != null && input.length() > 0){
			output = input.replaceAll(" ","");
		}
		return output;
	}

	public boolean checkColValue(String volume, String open, String high,
			String low, String close) {
		boolean isCheck = false;
		try {
			Double.parseDouble(volume);
			Double.parseDouble(open);
			Double.parseDouble(high);
			Double.parseDouble(low);
			Double.parseDouble(close);
			isCheck = true;
		} catch (Exception e) {
			// 空值、"--"
			isCheck = false;
		}

		return isCheck;
	}
	
	/**
	 * 下載上市股票資料
	 * @param filePath
	 * @param initDate
	 * @param lastFileTime
	 */
	public void toDownloadTse() {
//		String url = "http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX3_print.php?genpage=genpage/Report201205/A11220120508ALLBUT0999_1.php&type=csv"; //檔案路徑
//		String file = "D:/Report201205/report20120508.csv"; //
		
		this.connectDatabase();
		
		/*
		 * SwingWorker(T,V)
		 * T：為最終結果集 【<T> the result type returned by this SwingWorker's doInBackground and get methods】，
		 *    由文檔的介紹可以知道這個結果可以被doInBackground和get方法返回。
		 * V：為中間結果集【<V> the type used for carrying out intermediate results by this SwingWorker's publish and process methods】，
		 *    publish and process方法填值、取值。
		 */
		new SwingWorker<Integer, String>() {  //new SwingWorker
			@Override
			protected Integer doInBackground() {
				Integer count = 0;
				
				Date lastFileTime = queryLastUpdateTime(Constant.STOCK_CLASS_TSE);
				String filePath = stockTsePath;
				
				publish("=== 開始下載TSE資料檔 ===");
				try {
					int defaultYear = 0;
					int defaultMonth = 0;
					int defaultDay = 0;
					
					Calendar calendar = Calendar.getInstance();
					int nowYear = calendar.get(Calendar.YEAR);
					int nowMonth = calendar.get(Calendar.MONTH) + 1;
					int nowday = calendar.get(Calendar.DAY_OF_MONTH);
					
					if(lastFileTime == null){  //未下載過任何股票檔:從預設日期開始下載到現在日期
						//預設日期
						if(initDate != null && initDate.length() > 0){
							String[] initDateArr = initDate.split("/"); 
							defaultYear = Integer.valueOf(initDateArr[0]);	//預設年
							defaultMonth = Integer.valueOf(initDateArr[1]);	//預設月
							defaultDay = Integer.valueOf(initDateArr[2]);	//預設日
						}
						
						int difference = nowYear - Integer.valueOf(defaultYear);  //年的差值
						if(difference > 0){  //預設日期不在本年度
							for(int y = 0; y <= difference; y++){
								int aYear = (defaultYear+y);
								if(y == 0){  //取預設年份當年的值:從預設年月日取到年底
									for(int i = defaultMonth;i <= 12;i++){  //從預設月份開始取值
										String aMonth = String.format("%02d", i);
										
										if(i == defaultMonth){  //取預設月份的值:從預設日開始取到該月月底
											for(int j=defaultDay;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);  //放入publish method
													count++;
												}
											}
											
										}else{  //其他月份取一整個月的值
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}
									}
									
								}else if(y == difference){  //取現在年份到現在月日的值:從一月一日取到現在日期
									//從一月取到現在的月份
									for(int i=1;i<=nowMonth;i++){
										String aMonth = String.format("%02d", i);
										//依月份取值方式不同
										if(i < nowMonth){  //小於現在月份，取31天
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
											
										}else if(i == nowMonth){  //等於現在月份，取到現在日期
											for(int j=1;j<=nowday;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}
									}
									
								}else{  //其他年份:取一整年
									for(int i=1;i<=12;i++){
										String aMonth = String.format("%02d", i);
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
									}
								}
							}
							
						}else{  //預設日期在本年度:從預設日期取到現在日期
							int aYear = defaultYear;
							for(int i = defaultMonth;i <= nowMonth;i++){
								String aMonth = String.format("%02d", i);
								
								if(defaultMonth == nowMonth){  //同月份
									for(int j=defaultDay;j<=nowday;j++){  //從預設日開始取，取到今天
										String aDay = String.format("%02d", j);
										boolean isValidUrl = false;
										String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
										String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
										
										isValidUrl = checkUrl(url);
										if(isValidUrl){
											String showRate = saveToFile(url, file);
											publish("下載："+file+"，"+showRate);
											count++;
										}
									}
								}else{
									if(i == defaultMonth){  //取預設月份的值
										for(int j=defaultDay;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
										
									}else if(i == nowMonth){  //現在月份
										for(int j=1;j<=nowday;j++){  //取到現在日
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
										
									}else{  //其他月份:取一整個月的值
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
									}
								}
							}
						}
						
					}else{  //已下載過股票檔:最後一筆紀錄的日期加一天開始抓
						calendar.setTime(lastFileTime);  //最後一筆紀錄的日期
						calendar.add(Calendar.DATE,1);
						int recodeYear = calendar.get(Calendar.YEAR);
						int recodeMonth = calendar.get(Calendar.MONTH) + 1;
						int recodeday = calendar.get(Calendar.DAY_OF_MONTH);  //從最後紀錄日的隔天開始取值
						
						int difference = nowYear - Integer.valueOf(recodeYear);
						if(difference > 0){  //超過一年
							for(int y = 0; y <= difference; y++){
								int aYear = (recodeYear+y);
								
								if(y == 0){  //取記錄年份當年的值:從記錄年月日取到年底
									for(int i = recodeMonth;i <= 12;i++){
										String aMonth = String.format("%02d", i);
										if(i == recodeMonth){  //取記錄月份的值
											for(int j=recodeday;j<=31;j++){  //從最後紀錄日的隔天開始取值，取到月底
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}else{  //其他月份:取一整個月的值
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}
									}
									
								}else if(y == difference){ //取記錄年份的值:從一月取到現在日期
									for(int i=1;i<=nowMonth;i++){  //從一月開始取到現在月份
										String aMonth = String.format("%02d", i);
										//依月份取值方式不同
										if(i < nowMonth){  //小於現在月份，取31天
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
											
										}else{  //等於現在月份，取到現在日期
											for(int j=1;j<=nowday;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}
									}
									
								}else{  //其他年份:取一整年
									for(int i=1;i<=12;i++){
										String aMonth = String.format("%02d", i);
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
									}
								}
							}
						}else{  //最後記錄日期在本年度:從最後紀錄日期開始取值，取到現在日期
							int aYear = nowYear;
							for(int i = recodeMonth;i <= nowMonth;i++){
								String aMonth = String.format("%02d", i);
								//同年會有同月的情形
								if(recodeMonth == nowMonth){  //同月份，從記錄日取到今日
									for(int j=recodeday;j<=nowday;j++){  
										String aDay = String.format("%02d", j);
										boolean isValidUrl = false;
										String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
										String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
										
										isValidUrl = checkUrl(url);
										if(isValidUrl){
											String showRate = saveToFile(url, file);
											publish("下載："+file+"，"+showRate);
											count++;
										}
									}
								}else{  //不同月份
									if(i == recodeMonth){  //取記錄月份的值:從最後紀錄日期隔天開始取值，取到該月月底
										for(int j=recodeday;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
									}else if(i == nowMonth){  //取現在月份的值:取到現在日
										for(int j=1;j<=nowday;j++){  
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
										
									}else{  //其他月份:取一整個月的值
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
									}
								}
							}
						}
					}
				
				} catch (IOException e) {
					e.printStackTrace();
				}
				
				return count;
			}
			
			@Override  
			protected void process(List<String> chunks) {  //取出publish所存在的值，顯示在jTextArea上
			    for(String message:chunks){  
			    	jTextArea.append(message+"\n");
			    	jTextArea.setCaretPosition(jTextArea.getText().length());
			    }  
			}   

			@Override
			protected void done() {
				try {
					Integer totalCount = get();  //get()取得doInBackground的回傳值
					jTextArea.append("TSE下載完成，共下載"+totalCount+"個檔案!\n");
					if(totalCount == 0){
						jTextArea.append("TSE不需要更新資料!\n");
					}
					jTextArea.append("\n");
					jTextArea.setCaretPosition(jTextArea.getText().length());
					
					cancel(true);  //中斷執行緒
					dlOtcDataBtn.setEnabled(true);
			    	
				} catch (InterruptedException e) {
					e.printStackTrace();
				} catch (ExecutionException e) {
					e.printStackTrace();
				}  
			}
		}.execute();
		
	}
	
	/**
	 * 下載上櫃股票資料
	 * @param filePath
	 * @param initDate
	 * @param lastFileTime
	 */
	public void toDownloadOtc() {
//		String url = http://www.otc.org.tw/ch/stock/aftertrading/otc_quotes_no1430/SQUOTE_AL_1010605.CSV  //檔案路徑
//		String file = "D:/Report201205/report20120508.csv"; //
		
		new SwingWorker<Integer, String>() {  //new SwingWorker
			@Override
			protected Integer doInBackground() {
				Integer count = 0;
				
				Date lastFileTime = queryLastUpdateTime(Constant.STOCK_CLASS_OTC);  //最後一次上櫃資料更新時間
				String filePath = stockOtcPath;
				
				publish("=== 開始下載OTC資料檔 ===");
				try {
					int defaultYear = 0;
					int defaultMonth = 0;
					int defaultDay = 0;
					
					Calendar calendar = Calendar.getInstance();
					int nowYear = calendar.get(Calendar.YEAR);
					int nowMonth = calendar.get(Calendar.MONTH) + 1;
					int nowday = calendar.get(Calendar.DAY_OF_MONTH);
					
					if(lastFileTime == null){  //未下載過任何股票檔:從預設日期開始下載到現在日期
						//預設日期
						if(initDate != null && initDate.length() > 0){
							String[] initDateArr = initDate.split("/"); 
							defaultYear = Integer.valueOf(initDateArr[0]);	//預設年
							defaultMonth = Integer.valueOf(initDateArr[1]);	//預設月
							defaultDay = Integer.valueOf(initDateArr[2]);	//預設日
						}
						
						int difference = nowYear - Integer.valueOf(defaultYear);  //年的差值
						if(difference > 0){  //預設日期不在本年度
							for(int y = 0; y <= difference; y++){
								int aYear = (defaultYear+y);
								aYear = aYear - 1911;  //櫃買中心的年使用民國年
								if(y == 0){  //取預設年份當年的值:從預設年月日取到年底
									for(int i = defaultMonth;i <= 12;i++){  //從預設月份開始取值
										String aMonth = String.format("%02d", i);
										if(i == defaultMonth){  //取預設月份的值:從預設日開始取至該月月底
											for(int j=defaultDay;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}else{  //其他月份取一整個月的值
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}
									}
									
								}else if(y == difference){  //取現在年份到現在月日的值
									//從一月取到現在的月份
									for(int i=1;i<=nowMonth;i++){
										String aMonth = String.format("%02d", i);
										//依月份取值方式不同
										if(i < nowMonth){  //小於現在月份，取31天
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
											
										}else if(i == nowMonth){  //等於現在月份，從月初取到現在日
											for(int j=1;j<=nowday;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}
									}
									
								}else{  //其他年份:取一整年
									for(int i=1;i<=12;i++){
										String aMonth = String.format("%02d", i);
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
									}
								}
							}
							
						}else{  //預設日期在本年度:從預設日期取到現在日期
							int aYear = defaultYear;
							aYear = aYear - 1911;
							for(int i = defaultMonth;i <= nowMonth;i++){
								String aMonth = String.format("%02d", i);
								//同年會有同月的情形
								if(defaultMonth == nowMonth){  //同月份
									for(int j=defaultDay;j<=nowday;j++){  //從預設日取到現在日
										String aDay = String.format("%02d", j);
										boolean isValidUrl = false;
										String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
										String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
										
										isValidUrl = checkUrl(url);
										//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
										if(isValidUrl){
											String showRate = saveToFile(url, file);
											publish("下載："+file+"，"+showRate);
											count++;
										}
									}
									
								}else{  //不同月份
									if(i == defaultMonth){  //取預設月:從預設日取到月低
										for(int j=defaultDay;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
										
									}else if(i == nowMonth){  //現在月份:從月初取到現在日
										for(int j=1;j<=nowday;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
										
									}else{  //其他月份:取一整個月的值
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
									}
								}
							}
						}
						
					}else{  //已下載過股票檔:最後一筆紀錄的日期加一天開始抓
						calendar.setTime(lastFileTime);  //最後一筆紀錄的日期
						calendar.add(Calendar.DATE,1);
						int recodeYear = calendar.get(Calendar.YEAR);
						int recodeMonth = calendar.get(Calendar.MONTH) + 1;
						int recodeday = calendar.get(Calendar.DAY_OF_MONTH);  //從最後紀錄日的隔天開始取值
						
						int difference = nowYear - Integer.valueOf(recodeYear);
						if(difference > 0){  //超過一年
							for(int y = 0; y <= difference; y++){
								int aYear = (recodeYear+y);
								aYear = aYear - 1911;  //櫃買中心的年使用民國年
								
								if(y == 0){  //取記錄年份當年的值:從記錄年月日取到年底
									for(int i = recodeMonth;i <= 12;i++){
										String aMonth = String.format("%02d", i);
										if(i == recodeMonth){  //取記錄月份的值
											for(int j=recodeday;j<=31;j++){  //從最後紀錄日的隔天開始取值，取到月底
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}else{  //其他月份:取一整個月的值
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}
									}
									
								}else if(y == difference){ //取記錄年份的值:從一月取到現在日期
									for(int i=1;i<=nowMonth;i++){  //從一月開始取到現在月份
										String aMonth = String.format("%02d", i);
										//依月份取值方式不同
										if(i < nowMonth){  //小於現在月份，取31天
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
											
										}else{  //等於現在月份，取到現在日期
											for(int j=1;j<=nowday;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													count++;
												}
											}
										}
									}
									
								}else{  //其他年份:取一整年
									for(int i=1;i<=12;i++){
										String aMonth = String.format("%02d", i);
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
									}
								}
							}
						}else{  //最後記錄日期在本年度:從最後紀錄日期開始取值，取到現在日期
							int aYear = nowYear - 1911;  //民國年
							
							for(int i = recodeMonth;i <= nowMonth;i++){
								String aMonth = String.format("%02d", i);
								
								if(recodeMonth == nowMonth){  //同月份
									for(int j=recodeday;j<=nowday;j++){  
										String aDay = String.format("%02d", j);
										boolean isValidUrl = false;
										String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
										String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
										
										isValidUrl = checkUrl(url);
										//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
										if(isValidUrl){
											String showRate = saveToFile(url, file);
											publish("下載："+file+"，"+showRate);
											count++;
										}
									}
								}else{  //不同月份
									if(i == recodeMonth){  //取記錄月份的值:從最後紀錄日期隔天開始取值，取到該月月底
										for(int j=recodeday;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
									}else if(i == nowMonth){  //取現在月份的值:取到現在日
										for(int j=1;j<=nowday;j++){  
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
										
									}else{  //其他月份:取一整個月的值
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = filePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												count++;
											}
										}
									}
								}
							}
						}
					}
				
				} catch (IOException e) {
					e.printStackTrace();
				}
				
				return count;
			}
			
			@Override  
			protected void process(List<String> chunks) {  //取出publish所存在的值，顯示在jTextArea上
			    for(String message:chunks){  
			    	jTextArea.append(message+"\n");  
			    	jTextArea.setCaretPosition(jTextArea.getText().length());
			    }  
			}   

			@Override
			protected void done() {
				try {
			    	Integer totalCount = get();  //get()取得doInBackground的回傳值
					jTextArea.append("OTC下載完成，共下載"+totalCount+"個檔案!\n");
					if(totalCount == 0){
						jTextArea.append("OTC不需要更新資料\n");
					}
					jTextArea.append("\n");
			    	dlOtcDataBtn.setEnabled(false);
			    	updateDataBtn.setEnabled(true);
			    	
//			    	jTextArea.append("=== 資料檔均已下載完成 ===\n\n");  
//			    	jTextArea.append("請繼續執行「更新資料庫」...\n");
			    	jTextArea.setCaretPosition(jTextArea.getText().length());
			    	
			    	cancel(true);
				} catch (InterruptedException e) {
					e.printStackTrace();
				} catch (ExecutionException e) {
					e.printStackTrace();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}.execute();
		
	}
	
	public void toDownload() {
//		String url = "http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX3_print.php?genpage=genpage/Report201205/A11220120508ALLBUT0999_1.php&type=csv"; //檔案路徑
//		String file = "D:/Report201205/report20120508.csv"; //
		
		this.connectDatabase();
		
		/*
		 * SwingWorker(T,V)
		 * T：為最終結果集 【<T> the result type returned by this SwingWorker's doInBackground and get methods】，
		 *    由文檔的介紹可以知道這個結果可以被doInBackground和get方法返回。
		 * V：為中間結果集【<V> the type used for carrying out intermediate results by this SwingWorker's publish and process methods】，
		 *    publish and process方法填值、取值。
		 */
		new SwingWorker<Integer, String>() {  //new SwingWorker
			@Override
			protected Integer doInBackground() {
				
				Integer tseInt = 0;
				Date tseLastFileTime = queryLastUpdateTime(Constant.STOCK_CLASS_TSE);
				String tseFilePath = stockTsePath;
				
				publish("=== 開始下載TSE資料檔 ===");
				try {
					int defaultYear = 0;
					int defaultMonth = 0;
					int defaultDay = 0;
					
					Calendar calendar = Calendar.getInstance();
					int nowYear = calendar.get(Calendar.YEAR);
					int nowMonth = calendar.get(Calendar.MONTH) + 1;
					int nowday = calendar.get(Calendar.DAY_OF_MONTH);
					
					if(tseLastFileTime == null){  //未下載過任何股票檔:從預設日期開始下載到現在日期
						//預設日期
						if(initDate != null && initDate.length() > 0){
							String[] initDateArr = initDate.split("/"); 
							defaultYear = Integer.valueOf(initDateArr[0]);	//預設年
							defaultMonth = Integer.valueOf(initDateArr[1]);	//預設月
							defaultDay = Integer.valueOf(initDateArr[2]);	//預設日
						}
						
						int difference = nowYear - Integer.valueOf(defaultYear);  //年的差值
						if(difference > 0){  //預設日期不在本年度
							for(int y = 0; y <= difference; y++){
								int aYear = (defaultYear+y);
								if(y == 0){  //取預設年份當年的值:從預設年月日取到年底
									for(int i = defaultMonth;i <= 12;i++){  //從預設月份開始取值
										String aMonth = String.format("%02d", i);
										
										if(i == defaultMonth){  //取預設月份的值:從預設日開始取到該月月底
											for(int j=defaultDay;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);  //放入publish method
													tseInt++;
												}
											}
											
										}else{  //其他月份取一整個月的值
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													tseInt++;
												}
											}
										}
									}
									
								}else if(y == difference){  //取現在年份到現在月日的值:從一月一日取到現在日期
									//從一月取到現在的月份
									for(int i=1;i<=nowMonth;i++){
										String aMonth = String.format("%02d", i);
										//依月份取值方式不同
										if(i < nowMonth){  //小於現在月份，取31天
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													tseInt++;
												}
											}
											
										}else if(i == nowMonth){  //等於現在月份，取到現在日期
											for(int j=1;j<=nowday;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													tseInt++;
												}
											}
										}
									}
									
								}else{  //其他年份:取一整年
									for(int i=1;i<=12;i++){
										String aMonth = String.format("%02d", i);
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												tseInt++;
											}
										}
									}
								}
							}
							
						}else{  //預設日期在本年度:從預設日期取到現在日期
							int aYear = defaultYear;
							for(int i = defaultMonth;i <= nowMonth;i++){
								String aMonth = String.format("%02d", i);
								
								if(defaultMonth == nowMonth){  //同月份
									for(int j=defaultDay;j<=nowday;j++){  //從預設日開始取，取到今天
										String aDay = String.format("%02d", j);
										boolean isValidUrl = false;
										String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
										String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
										
										isValidUrl = checkUrl(url);
										if(isValidUrl){
											String showRate = saveToFile(url, file);
											publish("下載："+file+"，"+showRate);
											tseInt++;
										}
									}
								}else{
									if(i == defaultMonth){  //取預設月份的值
										for(int j=defaultDay;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												tseInt++;
											}
										}
										
									}else if(i == nowMonth){  //現在月份
										for(int j=1;j<=nowday;j++){  //取到現在日
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												tseInt++;
											}
										}
										
									}else{  //其他月份:取一整個月的值
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												tseInt++;
											}
										}
									}
								}
							}
						}
						
					}else{  //已下載過股票檔:最後一筆紀錄的日期加一天開始抓
						calendar.setTime(tseLastFileTime);  //最後一筆紀錄的日期
						calendar.add(Calendar.DATE,1);
						int recodeYear = calendar.get(Calendar.YEAR);
						int recodeMonth = calendar.get(Calendar.MONTH) + 1;
						int recodeday = calendar.get(Calendar.DAY_OF_MONTH);  //從最後紀錄日的隔天開始取值
						
						int difference = nowYear - Integer.valueOf(recodeYear);
						if(difference > 0){  //超過一年
							for(int y = 0; y <= difference; y++){
								int aYear = (recodeYear+y);
								
								if(y == 0){  //取記錄年份當年的值:從記錄年月日取到年底
									for(int i = recodeMonth;i <= 12;i++){
										String aMonth = String.format("%02d", i);
										if(i == recodeMonth){  //取記錄月份的值
											for(int j=recodeday;j<=31;j++){  //從最後紀錄日的隔天開始取值，取到月底
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													tseInt++;
												}
											}
										}else{  //其他月份:取一整個月的值
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													tseInt++;
												}
											}
										}
									}
									
								}else if(y == difference){ //取記錄年份的值:從一月取到現在日期
									for(int i=1;i<=nowMonth;i++){  //從一月開始取到現在月份
										String aMonth = String.format("%02d", i);
										//依月份取值方式不同
										if(i < nowMonth){  //小於現在月份，取31天
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													tseInt++;
												}
											}
											
										}else{  //等於現在月份，取到現在日期
											for(int j=1;j<=nowday;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
												String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													tseInt++;
												}
											}
										}
									}
									
								}else{  //其他年份:取一整年
									for(int i=1;i<=12;i++){
										String aMonth = String.format("%02d", i);
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												tseInt++;
											}
										}
									}
								}
							}
						}else{  //最後記錄日期在本年度:從最後紀錄日期開始取值，取到現在日期
							int aYear = nowYear;
							for(int i = recodeMonth;i <= nowMonth;i++){
								String aMonth = String.format("%02d", i);
								//同年會有同月的情形
								if(recodeMonth == nowMonth){  //同月份，從記錄日取到今日
									for(int j=recodeday;j<=nowday;j++){  
										String aDay = String.format("%02d", j);
										boolean isValidUrl = false;
										String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
										String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
										
										isValidUrl = checkUrl(url);
										if(isValidUrl){
											String showRate = saveToFile(url, file);
											publish("下載："+file+"，"+showRate);
											tseInt++;
										}
									}
								}else{  //不同月份
									if(i == recodeMonth){  //取記錄月份的值:從最後紀錄日期隔天開始取值，取到該月月底
										for(int j=recodeday;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												tseInt++;
											}
										}
									}else if(i == nowMonth){  //取現在月份的值:取到現在日
										for(int j=1;j<=nowday;j++){  
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												tseInt++;
											}
										}
										
									}else{  //其他月份:取一整個月的值
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.TSE_URL, aYear, aMonth, aYear, aMonth, aDay);
											String file = tseFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												tseInt++;
											}
										}
									}
								}
							}
						}
					}
				
				} catch (IOException e) {
					e.printStackTrace();
				}
				
				tseCount = tseInt;

				Integer otcInt = 0;
				Date otcLastFileTime = queryLastUpdateTime(Constant.STOCK_CLASS_OTC);  //最後一次上櫃資料更新時間
				String otcFilePath = stockOtcPath;
				
				publish("=== 開始下載OTC資料檔 ===");
				try {
					int defaultYear = 0;
					int defaultMonth = 0;
					int defaultDay = 0;
					
					Calendar calendar = Calendar.getInstance();
					int nowYear = calendar.get(Calendar.YEAR);
					int nowMonth = calendar.get(Calendar.MONTH) + 1;
					int nowday = calendar.get(Calendar.DAY_OF_MONTH);
					
					if(otcLastFileTime == null){  //未下載過任何股票檔:從預設日期開始下載到現在日期
						//預設日期
						if(initDate != null && initDate.length() > 0){
							String[] initDateArr = initDate.split("/"); 
							defaultYear = Integer.valueOf(initDateArr[0]);	//預設年
							defaultMonth = Integer.valueOf(initDateArr[1]);	//預設月
							defaultDay = Integer.valueOf(initDateArr[2]);	//預設日
						}
						
						int difference = nowYear - Integer.valueOf(defaultYear);  //年的差值
						if(difference > 0){  //預設日期不在本年度
							for(int y = 0; y <= difference; y++){
								int aYear = (defaultYear+y);
								aYear = aYear - 1911;  //櫃買中心的年使用民國年
								if(y == 0){  //取預設年份當年的值:從預設年月日取到年底
									for(int i = defaultMonth;i <= 12;i++){  //從預設月份開始取值
										String aMonth = String.format("%02d", i);
										if(i == defaultMonth){  //取預設月份的值:從預設日開始取至該月月底
											for(int j=defaultDay;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													otcInt++;
												}
											}
										}else{  //其他月份取一整個月的值
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													otcInt++;
												}
											}
										}
									}
									
								}else if(y == difference){  //取現在年份到現在月日的值
									//從一月取到現在的月份
									for(int i=1;i<=nowMonth;i++){
										String aMonth = String.format("%02d", i);
										//依月份取值方式不同
										if(i < nowMonth){  //小於現在月份，取31天
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													otcInt++;
												}
											}
											
										}else if(i == nowMonth){  //等於現在月份，從月初取到現在日
											for(int j=1;j<=nowday;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													otcInt++;
												}
											}
										}
									}
									
								}else{  //其他年份:取一整年
									for(int i=1;i<=12;i++){
										String aMonth = String.format("%02d", i);
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												otcInt++;
											}
										}
									}
								}
							}
							
						}else{  //預設日期在本年度:從預設日期取到現在日期
							int aYear = defaultYear;
							aYear = aYear - 1911;
							for(int i = defaultMonth;i <= nowMonth;i++){
								String aMonth = String.format("%02d", i);
								//同年會有同月的情形
								if(defaultMonth == nowMonth){  //同月份
									for(int j=defaultDay;j<=nowday;j++){  //從預設日取到現在日
										String aDay = String.format("%02d", j);
										boolean isValidUrl = false;
										String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
										String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
										
										isValidUrl = checkUrl(url);
										//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
										if(isValidUrl){
											String showRate = saveToFile(url, file);
											publish("下載："+file+"，"+showRate);
											otcInt++;
										}
									}
									
								}else{  //不同月份
									if(i == defaultMonth){  //取預設月:從預設日取到月低
										for(int j=defaultDay;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												otcInt++;
											}
										}
										
									}else if(i == nowMonth){  //現在月份:從月初取到現在日
										for(int j=1;j<=nowday;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												otcInt++;
											}
										}
										
									}else{  //其他月份:取一整個月的值
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												otcInt++;
											}
										}
									}
								}
							}
						}
						
					}else{  //已下載過股票檔:最後一筆紀錄的日期加一天開始抓
						calendar.setTime(otcLastFileTime);  //最後一筆紀錄的日期
						calendar.add(Calendar.DATE,1);
						int recodeYear = calendar.get(Calendar.YEAR);
						int recodeMonth = calendar.get(Calendar.MONTH) + 1;
						int recodeday = calendar.get(Calendar.DAY_OF_MONTH);  //從最後紀錄日的隔天開始取值
						
						int difference = nowYear - Integer.valueOf(recodeYear);
						if(difference > 0){  //超過一年
							for(int y = 0; y <= difference; y++){
								int aYear = (recodeYear+y);
								aYear = aYear - 1911;  //櫃買中心的年使用民國年
								
								if(y == 0){  //取記錄年份當年的值:從記錄年月日取到年底
									for(int i = recodeMonth;i <= 12;i++){
										String aMonth = String.format("%02d", i);
										if(i == recodeMonth){  //取記錄月份的值
											for(int j=recodeday;j<=31;j++){  //從最後紀錄日的隔天開始取值，取到月底
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													otcInt++;
												}
											}
										}else{  //其他月份:取一整個月的值
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													otcInt++;
												}
											}
										}
									}
									
								}else if(y == difference){ //取記錄年份的值:從一月取到現在日期
									for(int i=1;i<=nowMonth;i++){  //從一月開始取到現在月份
										String aMonth = String.format("%02d", i);
										//依月份取值方式不同
										if(i < nowMonth){  //小於現在月份，取31天
											for(int j=1;j<=31;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													otcInt++;
												}
											}
											
										}else{  //等於現在月份，取到現在日期
											for(int j=1;j<=nowday;j++){
												String aDay = String.format("%02d", j);
												boolean isValidUrl = false;
												String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
												String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
												
												isValidUrl = checkUrl(url);
												//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
												if(isValidUrl){
													String showRate = saveToFile(url, file);
													publish("下載："+file+"，"+showRate);
													otcInt++;
												}
											}
										}
									}
									
								}else{  //其他年份:取一整年
									for(int i=1;i<=12;i++){
										String aMonth = String.format("%02d", i);
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												otcInt++;
											}
										}
									}
								}
							}
						}else{  //最後記錄日期在本年度:從最後紀錄日期開始取值，取到現在日期
							int aYear = nowYear - 1911;  //民國年
							
							for(int i = recodeMonth;i <= nowMonth;i++){
								String aMonth = String.format("%02d", i);
								
								if(recodeMonth == nowMonth){  //同月份
									for(int j=recodeday;j<=nowday;j++){  
										String aDay = String.format("%02d", j);
										boolean isValidUrl = false;
										String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
										String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
										
										isValidUrl = checkUrl(url);
										//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
										if(isValidUrl){
											String showRate = saveToFile(url, file);
											publish("下載："+file+"，"+showRate);
											otcInt++;
										}
									}
								}else{  //不同月份
									if(i == recodeMonth){  //取記錄月份的值:從最後紀錄日期隔天開始取值，取到該月月底
										for(int j=recodeday;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												otcInt++;
											}
										}
									}else if(i == nowMonth){  //取現在月份的值:取到現在日
										for(int j=1;j<=nowday;j++){  
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												otcInt++;
											}
										}
										
									}else{  //其他月份:取一整個月的值
										for(int j=1;j<=31;j++){
											String aDay = String.format("%02d", j);
											boolean isValidUrl = false;
											String url = String.format(Constant.OTC_URL, aYear, aMonth, aDay);
											String file = otcFilePath.concat(aYear+aMonth).concat("/").concat(aYear+aMonth+aDay.concat(".csv"));
											
											isValidUrl = checkUrl(url);
											//publish("檔案URL:"+url+",連結是否有效："+isValidUrl);
											if(isValidUrl){
												String showRate = saveToFile(url, file);
												publish("下載："+file+"，"+showRate);
												otcInt++;
											}
										}
									}
								}
							}
						}
					}
				
				} catch (IOException e) {
					e.printStackTrace();
				}
				
				otcCount = otcInt;
				
				return tseInt+otcInt;
			}
			
			@Override  
			protected void process(List<String> chunks) {  //取出publish所存在的值，顯示在jTextArea上
			    for(String message:chunks){  
			    	jTextArea.append(message+"\n");
			    	jTextArea.setCaretPosition(jTextArea.getText().length());
			    }  
			}   

			@Override
			protected void done() {
				try {
					Integer totalCount = get();  //get()取得doInBackground的回傳值
					
					jTextArea.append("=== 資料檔下載完成 ===\n");
					jTextArea.append("TSE：下載"+tseCount+"個檔案!\n");
					if(tseCount == 0){
						jTextArea.append("TSE不需要更新資料!\n");
					}
					jTextArea.append("OTC：下載"+otcCount+"個檔案!\n");
					if(otcCount == 0){
						jTextArea.append("OTC不需要更新資料!\n");
					}
					if(totalCount != 0){
						jTextArea.append("總共下載"+totalCount+"個檔案!\n");
					}
					
					jTextArea.append("\n");
					jTextArea.setCaretPosition(jTextArea.getText().length());
					
					cancel(true);  //中斷執行緒
					
					dlDataBtn.setEnabled(false);
					updateDataBtn.setEnabled(true);  //開始「更新資料庫」按鈕
			    	
				} catch (Exception e) {
					e.printStackTrace();
					dlDataBtn.setEnabled(true);  //下載失敗，下載按鈕enabled
				}  
			}
		}.execute();
		
	}

	/**
	 * 將Stream寫成檔案
	 * @param addressUrl
	 * @param fileName
	 * @throws IOException
	 */
	public String saveToFile(String addressUrl, String fileName) {
		int BUFFER_SIZE = 51200;
		long AllSize = 0;
		DecimalFormat twoDigits = new DecimalFormat("0.00");

		FileOutputStream fileOutputStream = null;
		
		BufferedInputStream bufferedInputStream = null;
		HttpURLConnection httpUrl = null;
		URL url = null;
		String showRate = "";
		
		try {
			byte[] buf = new byte[BUFFER_SIZE];
			int downloadSize = 0;

			url = new URL(addressUrl);
			httpUrl = (HttpURLConnection) url.openConnection();
			
//			long count = Long.parseLong(httpUrl.getHeaderField("Content-Length"));
			// 2014 otc改版後抓不到httpUrl.getHeaderField("Content-Length")，無法一開始就知道檔案大小
			long fileTotalSize = 0;
			String contentLength = httpUrl.getHeaderField("Content-Length");
			if(contentLength != null && contentLength.length() > 0){
				fileTotalSize = Long.parseLong(contentLength);
			}
			
			Map<String, List<String>> headerFields = httpUrl.getHeaderFields();  // 可以取得header資料，如檔案大小
			List<String> contentDispositon = headerFields.get("Content-Disposition");
			System.out.println("Content-Disposition: "+contentDispositon);
			
			
			httpUrl.connect();
			bufferedInputStream = new BufferedInputStream(httpUrl.getInputStream());
			
			File file = new File(fileName);
			if (!file.exists()) {
				if (!file.getParentFile().exists()) {
					file.getParentFile().mkdirs();
				}
				file.createNewFile();
			}
			fileOutputStream = new FileOutputStream(fileName);

			if (DEBUG){
				System.out.println("檔案url[" + addressUrl + "]\n儲存為[" + fileName + "]");
			}

			while ((downloadSize = bufferedInputStream.read(buf)) != -1) {
				fileOutputStream.write(buf, 0, downloadSize);
				AllSize += downloadSize;
				DownloadSpeed += downloadSize;
				if (DEBUG){
					System.out.println("downloadSize=" + downloadSize + "-----AllSize="	+ AllSize);
					System.out.println("download rate: "+ (twoDigits.format((double) (AllSize * 100) / fileTotalSize))	+ "%");
				}
			}
			
//			showRate = (AllSize * 100) / count + " %，" + Math.round(AllSize / 1024) + " KB";
			if(contentLength != null && contentLength.length() > 0 && fileTotalSize > 0){
				showRate = (AllSize * 100) / fileTotalSize + " %，" + Math.round(AllSize / 1024) + " KB 完成!";
			}else{
				showRate = "100 %，" + Math.round(AllSize / 1024) + " KB 完成!";
			}
			
//			fo = false;
			fileOutputStream.flush();
			fileOutputStream.close();
			bufferedInputStream.close();
			httpUrl.disconnect();
		} catch (Exception e) {
			System.out.println("saveToFile has some err...");
			System.out.println(e.getMessage());
		} finally {
			if(fileOutputStream != null){
				try {
					fileOutputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			
			if(bufferedInputStream != null){
				try {
					bufferedInputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		
		return showRate;
	}
	
	/**
	 * 讀取檔案及寫入stock_tse
	 * @param stockPath
	 * @throws Exception
	 */
	public boolean readAndInsertTse(String stockPath)throws Exception {
		System.out.println("readAndInsertTse...");
		System.out.println("stockPath="+stockPath);
		
		boolean isFinish = false;
		File rootFolder = new File(stockPath);
		List<File> fileList = this.getTseFilePathList(rootFolder);
		
		if(fileList != null && fileList.size() > 0){
			Statement statement = conn.createStatement();
			Statement statement2 = conn.createStatement();
			conn.setAutoCommit(false);
			
			StringBuffer insertStockTseDetail = new StringBuffer("insert into `STOCK_DETAIL`(`STOCK_CODE`,`VOLUME`,`OPEN`,`HIGH`,`LOW`,`CLOSE`,`UP_DN`,`TRANSACTION_DATE`) values ");
			StringBuffer insertTseValue = new StringBuffer();
			
			StringBuffer insertStockUpdateLog = new StringBuffer("insert into `stock_update_log`(`FILE_NAME`, `FILE_TIME`,`STOCK_CLASS`) values");
			StringBuffer insertLogValue = new StringBuffer();
			CsvReader reader = null;
			try {
				for (File file : fileList) {
					if(file != null){
						String fileAllName = file.getName();
//						String fileName = fileAllName.substring(0, fileAllName.indexOf(".csv"));
						String twYear = fileAllName.substring(0, 4);
						String month = fileAllName.substring(4, 6);
						String day = fileAllName.substring(6, 8);
			
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
						Date transactionDate = sdf.parse(twYear + "/" + month + "/" + day);
			
						// 組合欲取資料之table title
						String title = String.format("%3d年%02d月%02d日", Integer.valueOf(twYear) - 1911, Integer.valueOf(month), Integer.valueOf(day));
						title = title.concat("每日收盤行情(全部");
			
						// CsvReader讀取csv檔
//						jTextArea.append("讀取檔案："+file.getPath()+"\n"); 
//						jTextArea.setCaretPosition(jTextArea.getText().length());
						
						String csvFilePath = file.getPath();
						reader = new CsvReader(csvFilePath, ',', Charset.forName("BIG5")); // 編碼

						boolean isGet = false;
						while (reader.readRecord()) {
							String col_0 = reader.getValues()[0];	//代號

							// 該檔案非開盤日，跳過整份檔案
							if (col_0 != "" && col_0.indexOf("查無資料") != -1) {
//								jTextArea.append(fileName + " 非開盤日...\n");
								break;
							}

							if (col_0 != "" && col_0.indexOf(Constant.TCC_CODE) == -1 && isGet == false) {  //略過col_0不為TCC_CODE的資料
								continue;
							}else{
//									System.out.println("找到要擷取的table的第一筆資料:"+TCC_CODE);
								isGet = true;
							}

							if (isGet) {
//									if (col_0 != "" && col_0.indexOf("證券代號") != -1) { // 下一筆開始取值
//										continue;
//									}
								if ((col_0 != "" && col_0.indexOf("漲跌符號說明") != -1) || col_0 == "") { // 資料結尾
									break;
								}

//									col_0 = col_0.replaceAll("=", "").replaceAll("\"", ""); // 證券代號欄位會多出=和"符號(從1101開始取就無這個問題)
								// String col_1 = reader.getValues()[1]; //證券名稱
								String col_2 = (reader.getValues() != null && reader.getValues()[2] != "") ? trimSpace(reader.getValues()[2]) : "0";  // 成交股數(成交量)，資料不符合欄位型類填入預設值0
												col_2 = col_2.replaceAll(",", "");  //先去除金額中的「,」符號，再做是否為數字判斷
												col_2 = isInteger(col_2) ? col_2 : "0";
								
								// String col_3 = reader.getValues()[3]; //成交筆數
								// String col_4 = reader.getValues()[4]; //成交金額
								String col_5 = (reader.getValues() != null && reader.getValues()[5] != "" && isDouble(trimSpace(reader.getValues()[5]))) ? 
												trimSpace(reader.getValues()[5]) : "0.00"; // 開盤價，資料不符合欄位型類填入預設值
								String col_6 = (reader.getValues() != null && reader.getValues()[6] != "" &&  isDouble(trimSpace(reader.getValues()[6]))) ? 
												trimSpace(reader.getValues()[6]) : "0.00"; // 最高價，資料不符合欄位型類填入預設值
								String col_7 = (reader.getValues() != null && reader.getValues()[7] != "" &&  isDouble(trimSpace(reader.getValues()[7]))) ? 
												trimSpace(reader.getValues()[7]) : "0.00"; // 最低價，資料不符合欄位型類填入預設值
								String col_8 = (reader.getValues() != null && reader.getValues()[8] != "" &&  isDouble(trimSpace(reader.getValues()[8]))) ? 
												trimSpace(reader.getValues()[8]) : "0.00"; // 收盤價，資料不符合欄位型類填入預設值
												
								String col_9 = (reader.getValues() != null && reader.getValues()[9] != "") ? trimSpace(reader.getValues()[9]) : ""; // 漲跌(+/-)
								String col_10 = (reader.getValues() != null && reader.getValues()[10] != "" &&  isDouble(trimSpace(reader.getValues()[10]))) ? 
												trimSpace(reader.getValues()[10]) : "0.00"; // 漲跌價差，資料不符合欄位型類填入預設值

								//將+/-欄位與漲跌價差合併成一個欄位
								String up_dn = "";
								if(Constant.MINUS_SYMBOL.equals(col_9)){
									up_dn = "-"+col_10;String.valueOf((0 - Double.parseDouble(col_10)));
								}else{
									up_dn = col_10;
								}
								
								//組合多筆tse sql
								insertTseValue.append(",('"+col_0+"',"+col_2+","+col_5+","+col_6+","+col_7+","+col_8+","+up_dn+",'"+new java.sql.Date(transactionDate.getTime())+"')");
							}
						}
						//組合多筆log sql
						String lastUpdateFileName = fileAllName;
						Date lastUpdateFileTime = new java.sql.Date(transactionDate.getTime());
						insertLogValue = insertLogValue.append(",('"+lastUpdateFileName+"','"+lastUpdateFileTime+"','"+Constant.STOCK_CLASS_TSE+"')");
					}
				}
				
				if((insertTseValue != null && insertTseValue.length() > 0) && (insertLogValue != null && insertLogValue.length() > 0)){
					//讀取所有資料一次寫入
					String sql = insertStockTseDetail.append(insertTseValue.substring(1,insertTseValue.length())).toString();
					statement.execute(sql);
					jTextArea.append("TSE資料寫入資料庫!\n");
					
					//寫入更新記錄檔
					String sql2 = insertStockUpdateLog.append(insertLogValue.substring(1,insertLogValue.length())).toString();
					statement2.execute(sql2);
					
					jTextArea.append("TSE資料更新完成!\n");
					jTextArea.setCaretPosition(jTextArea.getText().length());
					isFinish = true;
				}else{
					jTextArea.append("TSE無資料可更新!\n");
				}
				
				reader.close();
				statement.close();
				statement2.close();
				
			} catch (Exception e) {
				jTextArea.append("TSE寫入資料發生問題!資料庫rollback...\n");
				if (conn != null) {
		            try {
		            	e.printStackTrace();
		                conn.rollback();
		            } catch(SQLException excep) {
		            	e.printStackTrace();
		            }
		        }
			} finally {
				if (reader != null) {
					reader.close();
		        }
				if (statement != null) {
					statement.close();
		        }
				if (statement2 != null) {
					statement2.close();
		        }
				conn.setAutoCommit(true);
//				conn.close();
			}
		}
		
		return isFinish;
	}
	
	/**
	 * 讀取檔案及寫入stock_otc
	 * @param stockPath
	 * @throws Exception
	 */
	public boolean readAndInsertOtc(String stockPath)throws Exception {
		System.out.println("readAndInsertOtc...");
		System.out.println("stockPath="+stockPath);
		boolean isFinish = false;
		File rootFolder = new File(stockPath);
		List<File> fileList = this.getOtcFilePathList(rootFolder);
		
		if(fileList != null && fileList.size() > 0){
			Statement statement = conn.createStatement();
			Statement statement2 = conn.createStatement();
			conn.setAutoCommit(false);
			
			StringBuffer insertStockOtcDetail =	new StringBuffer("insert into `STOCK_DETAIL`(`STOCK_CODE`,`VOLUME`,`OPEN`,`HIGH`,`LOW`,`CLOSE`,`UP_DN`,`TRANSACTION_DATE`) values ");
			StringBuffer insertOtcValue = new StringBuffer();
			
			StringBuffer insertStockUpdateLog = new StringBuffer("insert into stock_update_log(FILE_NAME, FILE_TIME,STOCK_CLASS) values");
			StringBuffer insertLogValue = new StringBuffer();
			CsvReader reader = null;
			try {
				for (File file : fileList) {
					if(file != null){
						String fileAllName = file.getName();
//						String fileName = fileAllName.substring(0, fileAllName.indexOf(".csv"));
						String twYear = fileAllName.substring(0, 3);
						String month = fileAllName.substring(3, 5);
						String day = fileAllName.substring(5, 7);
			
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
						Date transactionDate = sdf.parse(Integer.valueOf(twYear) + 1911 + "/" + month + "/" + day);
			
						// 組合欲取資料之table title
						String title = String.format("%3d年%02d月%02d日", Integer.valueOf(twYear), Integer.valueOf(month), Integer.valueOf(day));
						title = title.concat(" 股票行情");
			
						// CsvReader讀取csv檔
//						jTextArea.append("讀取檔案："+file.getPath()+"\n");
//						jTextArea.setCaretPosition(jTextArea.getText().length());
						
						String csvFilePath = file.getPath();
						reader = new CsvReader(csvFilePath, ',', Charset.forName("BIG5")); // 編碼

						boolean isGet = true;
						int rowCount = 0;
						while (reader.readRecord()) {
							String col_0 = reader.getValues()[0];	//代號

//							// 該檔案非開盤日，跳過整份檔案
//							if (col_0 != "" && col_0.indexOf("查無資料") != -1) {
////								jTextArea.append(fileName + " 非開盤日...\n");
//								// System.out.println(fileName+" 未開盤...\n");
//								break;
//							}
//							
//							if (col_0 != "" && col_0.indexOf("代號") != -1 && isGet == false) {  //從「代號」下一筆開始抓
//								isGet = true;
//								continue;
//							}

//								if (col_0 != "" && col_0.indexOf(TCC_CODE) == -1 && isGet == false) {  //略過col_0不為TCC_CODE的資料
//									continue;
//								}else{
////									jTextArea.append("找到要擷取的table的第一筆資料:"+TCC_CODE);
//									isGet = true;
//								}
							
							if(rowCount <= 3){
								rowCount++;
								continue;
							}

							if (isGet) {
//									if (col_0 != "" && col_0.indexOf("證券代號") != -1) { // 下一筆開始取值
//										continue;
//									}
//									if ((col_0 != "" && col_0.indexOf("漲跌符號說明") != -1) || col_0 == "") { // 資料結尾
//										break;
//									}
								
								//「代號」為六位數且為7開頭的不抓，最後一欄"共xx筆"
								String firstString = col_0.substring(0,1);
								if (col_0 != "" && ((col_0.length() == 6 && firstString.equals("7")) || "共".equals(firstString))){  
									continue;
								}

								String col_1 = reader.getValues()[1]; //證券名稱
								String col_2 = (reader.getValues() != null && reader.getValues()[2] != "" &&  isDouble(trimSpace(reader.getValues()[2]))) ? 
												trimSpace(reader.getValues()[2]) : "0.00"; // 收盤 ，資料不符合欄位型類填入預設值
								String col_3 = (reader.getValues() != null && reader.getValues()[3] != "" &&  isDouble(trimSpace(reader.getValues()[3]))) ? 
												trimSpace(reader.getValues()[3]) : "0.00"; // 漲跌，資料不符合欄位型類填入預設值
								String col_4 = (reader.getValues() != null && reader.getValues()[4] != "" && isDouble(trimSpace(reader.getValues()[4]))) ? 
												trimSpace(reader.getValues()[4]) : "0.00"; // 開盤，資料不符合欄位型類填入預設值
								String col_5 = (reader.getValues() != null && reader.getValues()[5] != "" &&  isDouble(trimSpace(reader.getValues()[5]))) ? 
												trimSpace(reader.getValues()[5]) : "0.00"; // 最高，資料不符合欄位型類填入預設值
								String col_6 = (reader.getValues() != null && reader.getValues()[6] != "" &&  isDouble(trimSpace(reader.getValues()[6]))) ? 
												trimSpace(reader.getValues()[6]) : "0.00"; // 最低，資料不符合欄位型類填入預設值
								String col_7 = (reader.getValues() != null && reader.getValues()[7] != "") ? trimSpace(reader.getValues()[7]) : "0";  // 成交股數(成交量)，資料不符合欄位型類填入預設值
												col_7 = col_7.replaceAll(",", "");  //先去除金額中的「,」符號，再做是否為數字判斷
												col_7 = isInteger(col_7) ? col_7 : "0";
								
								//組合多筆otc sql
								insertOtcValue = insertOtcValue.append(",('"+col_0+"',"+col_7+","+col_4+","+col_5+","+col_6+","+col_2+","+col_3+",'"+new java.sql.Date(transactionDate.getTime())+"')");
							}
						}
						//組合多筆log sql
						String lastUpdateFileName = fileAllName;
						Date lastUpdateFileTime = new java.sql.Date(transactionDate.getTime());
						insertLogValue.append(",('"+lastUpdateFileName+"','"+lastUpdateFileTime+"','"+Constant.STOCK_CLASS_OTC+"')");
					}
				}
				
				if((insertOtcValue != null && insertOtcValue.length() > 0) && (insertLogValue != null && insertLogValue.length() > 0)){
					//讀取所有資料一次寫入
					String sql = insertStockOtcDetail.append(insertOtcValue.substring(1,insertOtcValue.length())).toString();
					statement.execute(sql);
					jTextArea.append("OTC資料寫入資料庫!\n");
					
					String sql2 = insertStockUpdateLog.append(insertLogValue.substring(1,insertLogValue.length())).toString();
					statement2.execute(sql2);
					jTextArea.append("OTC資料更新完成!\n");
					jTextArea.setCaretPosition(jTextArea.getText().length());
					
					isFinish = true;
				}else{
					jTextArea.append("OTC無資料可更新!\n");
				}
				
				reader.close();
				statement.close();
				statement2.close();
				
			} catch (Exception e) {
				jTextArea.append("OTC寫入資料發生問題!資料庫rollback...\n");
				if (conn != null) {
		            try {
		            	e.printStackTrace();
		                conn.rollback();
		            } catch(SQLException excep) {
		            	e.printStackTrace();
		            }
		        }
			} finally {
				if (reader != null) {
					reader.close();
		        }
				if (statement != null) {
					statement.close();
		        }
				if (statement2 != null) {
					statement2.close();
		        }
				conn.setAutoCommit(true);
//				conn.close();
			}
		}
		
		return isFinish;
	}
	
    /**
     * 驗證URL所使用協定是否有效
     * @param urlString URL字串
     * @return boolean
     * @throws IOException 
     */
	public boolean checkUrl(String urlString) throws IOException {
        boolean check = false;
        
        if(urlString == null || urlString.length() == 0){
        	return check;
        }
        
        URL url = new URL(urlString);
        HttpURLConnection urlConnection = null;
        String protocol = url.getProtocol().toUpperCase();

        if ("HTTP".equals(protocol) || "HTTPS".equals(protocol)) {
            urlConnection = (HttpURLConnection) url.openConnection();
        }

        if (null != urlConnection) {
            urlConnection.setRequestMethod("GET");
            check = (urlConnection.getResponseCode() == HttpsURLConnection.HTTP_OK);
        }

        return check;
    }
	
	public Map<String, Object> checkUrlStatus(String urlString) throws IOException {
		Map<String, Object> map = new HashMap<String, Object>();
		
        boolean check = false;
        if(urlString == null || urlString.length() == 0){
        	map.put("exception", "url is null");
        }
        
        URL url = new URL(urlString);
        HttpURLConnection urlConnection = null;
        String protocol = url.getProtocol().toUpperCase();

        if ("HTTP".equals(protocol) || "HTTPS".equals(protocol)) {
            urlConnection = (HttpURLConnection) url.openConnection();
        }

        if (null != urlConnection) {
            urlConnection.setRequestMethod("GET");
            check = (urlConnection.getResponseCode() == HttpsURLConnection.HTTP_OK);
            map.put("responseCode", urlConnection.getResponseCode());
        }
        
        map.put("isConnected", check);

        return map;
    }
	
	/**
	 * append zero for 1 - 9
	 * @param input
	 * @return
	 */
	private String appendZero(String input){
		String output = "";
		
		if(input.length() == 1){
			output = "0".concat(input);
		}else{
			output = input;
		}
		
		return output;
	}

}